﻿CREATE TABLE [Mapping].[MappingItem]
(
	[Id] INT NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY,
  [Group] INT NOT NULL,
	[TargetKey] INT NOT NULL, 
	[Priority] INT NOT NULL,
  [Masked] BIT NOT NULL DEFAULT 0, 
  [Enabled] BIT NOT NULL DEFAULT 1,
	[Description] nvarchar(200) NULL, 
  CONSTRAINT [FK_MappingItem_TargetKey] FOREIGN KEY ([TargetKey]) REFERENCES [Mapping].[TargetKey]([Id]) ON DELETE CASCADE, 
  CONSTRAINT [FK_MappingItem_ToMappingGroup] FOREIGN KEY ([Group]) REFERENCES [Mapping].[MappingGroup]([Id]) ON DELETE CASCADE
)

GO

CREATE UNIQUE CLUSTERED INDEX [IX_MappingItem_Unique] ON [Mapping].[MappingItem] ([Group], [Priority], [TargetKey])

GO

CREATE TRIGGER [Mapping].[Trigger_MappingItem_Delete]
  ON [Mapping].[MappingItem]
  FOR DELETE
  AS
  BEGIN
    SET NoCount ON

    SELECT DISTINCT [Group] FROM deleted

    DECLARE curr CURSOR FOR SELECT DISTINCT [Group] FROM deleted

    OPEN curr

    DECLARE @groupId int
    
    FETCH NEXT FROM curr INTO @groupId
    WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC Mapping.GenerateRuntimeView @groupId
      FETCH NEXT FROM curr INTO @groupId
    END

    CLOSE curr
    DEALLOCATE curr
  END